package com.example.demo.service;

import com.example.demo.request.UserInfo;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// import static org.mockito.Answers.values;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Service
public class UserService {
    private final String url = "jdbc:sqlite:/var/www/html/data/database.db"; // 数据库连接字符串

    // 保存用户信息（如果用户存在则更新，否则插入）
    public void saveUserInfo(UserInfo userInfo) {
        if (userExists(userInfo.getUsername())) {
            updateUserInfo(userInfo); // 用户存在，更新信息
        } else {
            insertUser(userInfo); // 用户不存在，插入新信息
        }
    }

    // 插入用户信息到 users 表
    public void insertUser(UserInfo userInfo) {
        // 确保 userInfo 不为 null
        if (userInfo == null) {
            throw new IllegalArgumentException("UserInfo cannot be null");
        }

        // 构建 SQL 语句
        String sql = "INSERT INTO users";
        StringBuilder columns = new StringBuilder("(");
        StringBuilder values = new StringBuilder("VALUES (");
        List<Object> params = new ArrayList<>();

        // 使用 Map 存储字段及其值
        Map<String, Object> fieldMap = new HashMap<>();
        fieldMap.put("username", userInfo.getUsername());
        fieldMap.put("password", userInfo.getPassword());
        fieldMap.put("avatar", userInfo.getAvatar());
        fieldMap.put("name", userInfo.getName());
        fieldMap.put("email", userInfo.getEmail());
        fieldMap.put("phone", userInfo.getPhone());
        fieldMap.put("address", userInfo.getAddress());

        // 只插入有值的字段
        for (Map.Entry<String, Object> entry : fieldMap.entrySet()) {
            if (entry.getValue() != null) {
                columns.append(entry.getKey()).append(", ");
                values.append("?, ");
                params.add(entry.getValue());
            }
        }

        // 如果没有有效字段，则抛出异常
        if (params.isEmpty()) {
            throw new IllegalArgumentException("没有可插入的字段，至少需要一个字段有值");
        }

        // 移除最后的逗号和空格
        columns.setLength(columns.length() - 2);
        values.setLength(values.length() - 2);

        columns.append(")");
        values.append(")");

        sql += columns.toString() + " " + values.toString();

        // 执行 SQL 插入
        try (Connection conn = DriverManager.getConnection(url);
            PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 动态设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.err.println("SQL 错误信息: " + e.getMessage());
            e.printStackTrace(); // 打印堆栈跟踪以帮助调试
        }
    }
    
    // 更新用户信息到 users 表
    public void updateUserInfo(UserInfo userInfo) {
        String sql = "UPDATE users SET password = ?, avatar = ?, name = ?, email = ?, phone = ?, address = ? WHERE username = ?";

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userInfo.getPassword());
            pstmt.setString(2, userInfo.getAvatar());
            pstmt.setString(3, userInfo.getName());
            pstmt.setString(4, userInfo.getEmail());
            pstmt.setString(5, userInfo.getPhone());
            pstmt.setString(6, userInfo.getAddress());
            pstmt.setString(7, userInfo.getUsername());
            pstmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 检查用户是否存在
    public boolean userExists(String username) {
        String sql = "SELECT COUNT(*) FROM users WHERE username = ?";

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    // 验证密码
    public boolean validatePassword(String username, String password) {
        String sql = "SELECT password FROM users WHERE username = ?";

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getString("password").equals(password);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    // 根据用户名获取用户的完整信息
    public UserInfo getUserInfo(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        UserInfo userInfo = null;

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                userInfo = new UserInfo();
                userInfo.setId(rs.getInt("id"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setAvatar(rs.getString("avatar"));
                userInfo.setName(rs.getString("name"));
                userInfo.setEmail(rs.getString("email"));
                userInfo.setPhone(rs.getString("phone"));
                userInfo.setAddress(rs.getString("address"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userInfo;
    }

    // 获取所有用户信息
    public List<UserInfo> getAllUsers() {
        List<UserInfo> usersList = new ArrayList<>();
        String sql = "SELECT * FROM users";

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt("id"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setAvatar(rs.getString("avatar"));
                userInfo.setName(rs.getString("name"));
                userInfo.setEmail(rs.getString("email"));
                userInfo.setPhone(rs.getString("phone"));
                userInfo.setAddress(rs.getString("address"));
                usersList.add(userInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return usersList;
    }
}
